Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALTER CLUSTER

Purpose

Use the `ALTER` `CLUSTER` statement to redefine storage and parallelism characteristics of a cluster.

Note: You cannot use this statement to change the number or the name of columns in the cluster key, and you cannot change the tablespace in which the cluster is stored.

See Also: CREATE CLUSTER for information on creating a cluster, DROP CLUSTER and DROP TABLE for information on removing tables from a cluster, and CREATE TABLE ... physical_properties for information on adding a table to a cluster

Prerequisites

The cluster must be in your own schema or you must have the `ALTER` `ANY` `CLUSTER` system privilege.

Syntax

alter_cluster::=

Description of the illustration alter_cluster.gif

physical_attributes_clause ::=, size_clause::=, allocate_extent_clause ::=, deallocate_unused_clause ::=, parallel_clause::=

physical_attributes_clause ::=

Description of the illustration physical_attributes_clause.gif

storage_clause::=

allocate_extent_clause ::=

Description of the illustration allocate_extent_clause.gif

size_clause::=

deallocate_unused_clause ::=

Description of the illustration deallocate_unused_clause.gif

(size_clause::=)

parallel_clause::=

Description of the illustration parallel_clause.gif

Semantics

schema

Specify the schema containing the cluster. If you omit `schema`, Oracle Database assumes the cluster is in your own schema.

cluster

Specify the name of the cluster to be altered.

physical_attributes_clause

Use this clause to change the values of the `PCTUSED`, `PCTFREE`, and `INITRANS` parameters of the cluster.

Use the `STORAGE` clause to change the storage characteristics of the cluster.

See Also: physical_attributes_clause for information on the parameters storage_clause for a full description of that clause

Restriction on Physical Attributes You cannot change the values of the storage parameters `INITIAL` and `MINEXTENTS` for a cluster.

SIZE integer

Use the `SIZE` clause to specify the number of cluster keys that will be stored in data blocks allocated to the cluster.

Restriction on SIZE You can change the `SIZE` parameter only for an indexed cluster, not for a hash cluster.

See Also: CREATE CLUSTER for a description of the `SIZE` parameter and "Modifying a Cluster: Example"

allocate_extent_clause

Specify the `allocate_extent_clause` to explicitly allocate a new extent for the cluster.

When you explicitly allocate an extent with this clause, Oracle Database does not evaluate the storage parameters of the cluster and determine a new size for the next extent to be allocated (as it does when you create a table). Therefore, specify `SIZE` if you do not want Oracle Database to use a default value.

Restriction on Allocating Extents You can allocate a new extent only for an indexed cluster, not for a hash cluster.

See Also: allocate_extent_clause for a full description of this clause and "Deallocating Unused Space: Example"

deallocate_unused_clause

Use the `deallocate_unused_clause` to explicitly deallocate unused space at the end of the cluster and make the freed space available for other segments.

See Also: deallocate_unused_clause for a full description of this clause

CACHE | NOCACHE

This clause has the same behavior in `CREATE` `CLUSTER` and `ALTER` `CLUSTER` statements.

See Also: "CACHE | NOCACHE" for information on this clause.

parallel_clause

Specify the `parallel_clause` to change the default degree of parallelism for queries and `DML` on the cluster.

Restriction on Parallelized Clusters If the tables in `cluster` contain any columns of LOB or user-defined object type, this statement as well as subsequent `INSERT`, `UPDATE`, or `DELETE` operations on `cluster` are executed serially without notification.

See Also: parallel_clause in the documentation on `CREATE` `TABLE` for complete information on this clause

Examples

The following examples modify the clusters that were created in the `CREATE` `CLUSTER` "Examples".

Modifying a Cluster: Example The next statement alters the `personnel` cluster:

    ALTER CLUSTER personnel
       SIZE 1024 CACHE;

Oracle Database allocates 1024 bytes for each cluster key value and enables the cache attribute. Assuming a data block size of 2 kilobytes, future data blocks within this cluster contain 2 cluster keys in each data block, or 2 kilobytes divided by 1024 bytes.

Deallocating Unused Space: Example The following statement deallocates unused space from the `language` cluster, keeping 30 kilobytes of unused space for future use:

    ALTER CLUSTER language
       DEALLOCATE UNUSED KEEP 30 K;